package com.liu.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

public class DataDao {
    public Data selectById(int userId) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            Data data = new Data();
            connection = DBUtil.getConnection();
            String sqlToVistConutByTotal = "select count(*) as visitTotal from t_record r join t_blog b on b.blogId = r.blogId where b.userId = ?";
            statement = connection.prepareStatement(sqlToVistConutByTotal);
            statement.setInt(1, userId);
            resultSet = statement.executeQuery();
            if (resultSet.next()) data.setVisitCountByTotal(resultSet.getInt("visitTotal"));

            String sqlToCategoryTotal = "select count(distinct category) as categoryToTal from t_blog where userId = ?";
            statement = connection.prepareStatement(sqlToCategoryTotal);
            statement.setInt(1, userId);
            resultSet = statement.executeQuery();
            if (resultSet.next()) data.setCategoryCountByTotal(resultSet.getInt("categoryTotal"));

            String sqlToBlogTotal = "select count(*) as blogTotal from t_blog where userId = ?";
            statement = connection.prepareStatement(sqlToBlogTotal);
            statement.setInt(1, userId);
            resultSet = statement.executeQuery();
            if (resultSet.next()) data.setBlogCountByTotal(resultSet.getInt("blogTotal"));

            String sqlToVisitByMonth = "SELECT YEAR(visitTime) AS year, MONTH(visitTime) AS month, COUNT(*) AS perMonth FROM t_record WHERE blogId = any(SELECT blogId FROM t_blog WHERE userId = ?) GROUP BY YEAR(visitTime), MONTH(visitTime)";
            statement = connection.prepareStatement(sqlToVisitByMonth);
            statement.setInt(1, userId);
            resultSet = statement.executeQuery();
            Map<String, Integer> visitByMonth = new HashMap<>();
            while (resultSet.next()) {
                String year = resultSet.getString("year");
                String month = resultSet.getString("month");
                String time = year + "-" + month;
                int count = resultSet.getInt("perMonth");
                visitByMonth.put(time, count);
            }
            data.setVisitCountByMonth(visitByMonth);

            String sqlToVisitByAge = "SELECT age, COUNT(*) AS visitByAge FROM t_user WHERE userId IN (SELECT visitorId FROM t_record WHERE blogId = any(SELECT blogId FROM t_blog WHERE userId = ?)) GROUP BY age";
            statement = connection.prepareStatement(sqlToVisitByAge);
            statement.setInt(1, userId);
            resultSet = statement.executeQuery();
            Map<String, Integer> visitByAge = new HashMap<>();
            while (resultSet.next()) {
                String age = resultSet.getString("age");
                if (age.equals("暂无")) age = "未知";
                int count = resultSet.getInt("visitByAge");
                visitByAge.put(age, count);
            }
            data.setVisitCountByAge(visitByAge);

            String sqlToVisitByProfession = "SELECT profession, COUNT(*) AS visitByProfession FROM t_user WHERE userId IN (SELECT visitorId FROM t_record WHERE blogId = any(SELECT blogId FROM t_blog WHERE userId = ?)) GROUP BY profession";
            statement = connection.prepareStatement(sqlToVisitByProfession);
            statement.setInt(1, userId);
            resultSet = statement.executeQuery();
            Map<String, Integer> visitByProfession = new HashMap<>();
            while (resultSet.next()) {
                String profession = resultSet.getString("profession");
                if (profession.equals("暂无")) profession = "未知";
                int count = resultSet.getInt("visitByProfession");
                visitByProfession.put(profession, count);
            }
            data.setVisitCountByProfession(visitByProfession);

            String sqlToBlogByMonth = "SELECT YEAR(postTime) AS year, MONTH(postTime) AS month, COUNT(*) AS blogByMonth FROM t_blog WHERE userId = ? AND postTime BETWEEN DATE_SUB(NOW(), INTERVAL 1 YEAR) AND NOW() GROUP BY YEAR(postTime), MONTH(postTime)";
            statement = connection.prepareStatement(sqlToBlogByMonth);
            statement.setInt(1, userId);
            resultSet = statement.executeQuery();
            Map<String, Integer> blogByMonth = new HashMap<>();
            while (resultSet.next()) {
                String year = resultSet.getString("year");
                String month = resultSet.getString("month");
                String time = year + "-" + month;
                int count = resultSet.getInt("blogByMonth");
                blogByMonth.put(time, count);
            }
            data.setBlogCountByMonth(blogByMonth);

            String sqlToCategoryByDevide = "SELECT category, COUNT(*) AS categoryByDevide FROM t_blog WHERE userId = ? GROUP BY category";
            statement = connection.prepareStatement(sqlToCategoryByDevide);
            statement.setInt(1, userId);
            resultSet = statement.executeQuery();
            Map<String, Integer> categoryByDevide = new HashMap<>();
            while (resultSet.next()) {
                String catefory = resultSet.getString("category");
                int count = resultSet.getInt("categoryByDevide");
                categoryByDevide.put(catefory, count);
            }
            data.setCategoryCountByDevide(categoryByDevide);

            data.setUserId(userId);

            return data;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
}
